package com.common.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class DBUtil {
	
	private static String DB_URL;
	
	private static String DB_USERNAME;
	
	private static String DB_PASSWORD;
	
	static Connection conn;
	
	static {
		Properties p = new Properties();
		try {
			p.load(ProcessUtil.class.getResourceAsStream("/properties/db.properties"));
			DB_URL = p.getProperty("db.url");
			DB_USERNAME = p.getProperty("db.userName");
			DB_PASSWORD = p.getProperty("db.passWord");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 判断是否能查找到数据，没有where的情况
	 * @throws Exception 
	 */
	public static boolean isQueryData(String strsql) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		int rowcount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			rst = ptmt.executeQuery();
			while(rst.next()){
				rowcount++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return rowcount!=0;
	}
	
	/**
	 * 判断是否能查找到数据，有where的情况
	 * @throws Exception 
	 */
	public static boolean isQueryData(String strsql, Object[] objLst) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		int rowcount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			preparedstatementsetObject(ptmt, objLst);
			rst = ptmt.executeQuery();
			while(rst.next()){
				rowcount++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return rowcount!=0;
	}
	
	/**
	 * 计算查询出来的行数，没有where的情况
	 * @throws Exception 
	 */
	public static int getRowCount(String strsql) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		int rowcount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			rst = ptmt.executeQuery();
			while(rst.next()){
				rowcount++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return rowcount;
	}
	
	/**
	 * 计算查询出来的行数，有where的情况
	 * @throws Exception 
	 */
	public static int getRowCount(String strsql, Object[] objLst) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		int rowcount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			preparedstatementsetObject(ptmt, objLst);
			rst = ptmt.executeQuery();
			while(rst.next()){
				rowcount++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return rowcount;
	}
	
	/**
	 * 查找到某行某列名的值，没有where的情况
	 */
	public static String queryOneCell(String strsql) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		ResultSetMetaData rmd = null;
		String cell = "";
		int rowcount = 0;
		int columncount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			rst = ptmt.executeQuery();
			rmd = rst.getMetaData();
			columncount = rmd.getColumnCount();
			if(columncount>1){
				handlefailure("查到多列");
			}
			while(rst.next()){
				cell = rst.getObject(1).toString();
				rowcount++;
			}
			if(rowcount>1){
				handlefailure("查到多条数据");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return cell;
	}
	
	/**
	 * 查找到某行某列名的值，有where的情况
	 */
	public static String queryOneCell(String strsql, Object[] objLst) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		ResultSetMetaData rmd = null;
		String cell = "";
		int rowcount = 0;
		int columncount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			preparedstatementsetObject(ptmt, objLst);
			rst = ptmt.executeQuery();
			rmd = rst.getMetaData();
			columncount = rmd.getColumnCount();
			if(columncount>1){
				handlefailure("查到多列");
			}
			while(rst.next()){
				cell = rst.getObject(1).toString();
				rowcount++;
			}
			if(rowcount>1){
				handlefailure("查到多条数据");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return cell;
	}
	
	/**
	 * 查找某列多行的数据，把它们都放进一个ArrayList里，没有where的情况
	 */
	public static List<String> queryOneRowList(String strsql) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		ResultSetMetaData rmd = null;
		String cell = "";
		List<String> strLst = new ArrayList<String>();
		int columncount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			rst = ptmt.executeQuery();
			rmd = rst.getMetaData();
			columncount = rmd.getColumnCount();
			if(columncount>1){
				handlefailure("查到多列");
			}
			while(rst.next()){
				cell = rst.getObject(1).toString();
				strLst.add(cell);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return strLst;
	}
	
	/**
	 * 查找某列多行的数据，把它们都放进一个ArrayList里，有where的情况
	 */
	public static List<String> queryOneRowList(String strsql, Object[] objLst) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		ResultSetMetaData rmd = null;
		String cell = "";
		List<String> strLst = new ArrayList<String>();
		int columncount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			preparedstatementsetObject(ptmt, objLst);
			rst = ptmt.executeQuery();
			rmd = rst.getMetaData();
			columncount = rmd.getColumnCount();
			if(columncount>1){
				handlefailure("查到多列");
			}
			while(rst.next()){
				cell = rst.getObject(1).toString();
				strLst.add(cell);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return strLst;
	}
	
	/**
	 * 查找到某行多列的数据，列名放在一个map的key，列值放在map的value里，没
	 * 有where的情况
	 */
	public static Map<String, String> queryOneRow(String strsql) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		ResultSetMetaData rmd = null;
		Map<String, String> map = new HashMap<String, String>();
		int rowcount = 0;
		int columncount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			rst = ptmt.executeQuery();
			rmd = rst.getMetaData();
			columncount = rmd.getColumnCount();
			while(rst.next()){
				for(int i=1;i<=columncount;i++){
					if (rst.getObject(i) == null || "".equals(rst.getObject(i))){
						map.put(rmd.getColumnName(i), "");
					}
					else{
						map.put(rmd.getColumnName(i), rst.getObject(i).toString());
					}
				}
				rowcount++;
			}
			if(rowcount>1){
				handlefailure("查到多条数据");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return map;
	}
	
	/**
	 * 查找到某行多列的数据，列名放在一个map的key，列值放在map的value里，有
	 * where的情况
	 */
	public static Map<String, String> queryOneRow(String strsql, Object[] objLst) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		ResultSetMetaData rmd = null;
		Map<String, String> map = new HashMap<String, String>();
		int rowcount = 0;
		int columncount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			preparedstatementsetObject(ptmt, objLst);
			rst = ptmt.executeQuery();
			rmd = rst.getMetaData();
			columncount = rmd.getColumnCount();
			while(rst.next()){
				for(int i=1;i<=columncount;i++){
					if (rst.getObject(i) == null || "".equals(rst.getObject(i))){
						map.put(rmd.getColumnName(i), "");
					}
					else{
						map.put(rmd.getColumnName(i), rst.getObject(i).toString());
					}
				}
				rowcount++;
			}
			if(rowcount>1){
				handlefailure("查到多条数据");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return map;
	}
	
	/**
	 * 查找到多行多列的内容，字段为键，值为value，放在一个map里，没有where的情况
	 * @throws Exception 
	 */
	public static List<Map<String, String>> queryDataLst(String strsql) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		ResultSetMetaData rmd = null;
		List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
		int columncount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			rst = ptmt.executeQuery();
			rmd = rst.getMetaData();
			columncount = rmd.getColumnCount();
			while(rst.next()){
				Map<String, String> map = new HashMap<String, String>();
				for(int i=1;i<=columncount;i++){
					if(rst.getObject(i)==null){
						map.put(rmd.getColumnName(i), "");
					}else{
						map.put(rmd.getColumnName(i), rst.getObject(i).toString());
					}
				}
				mapList.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return mapList;
	}
	
	/**
	 * 查找到多行多列的内容，字段为键，值为value，放在一个map里，有where的情况
	 * @throws Exception 
	 */
	public static List<Map<String, String>> queryDataLst(String strsql, Object[] objLst) throws Exception{
		conn = getConnection();
		PreparedStatement ptmt = null;
		ResultSet rst = null;
		ResultSetMetaData rmd = null;
		List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
		int columncount = 0;
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			ptmt = conn.prepareStatement(strsql);
			preparedstatementsetObject(ptmt, objLst);
			rst = ptmt.executeQuery();
			rmd = rst.getMetaData();
			columncount = rmd.getColumnCount();
			while(rst.next()){
				Map<String, String> map = new HashMap<String, String>();
				for(int i=1;i<=columncount;i++){
					if(rst.getObject(i)==null){
						map.put(rmd.getColumnName(i), "");
					}else{
						map.put(rmd.getColumnName(i), rst.getObject(i).toString());
					}
				}
				mapList.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeResultSet(rst);
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
		return mapList;
	}
	
	/**
	 * 更新数据库，没有where的情况
	 * @throws Exception 
	 */
	public static void update(String strsql) throws Exception{
		conn = getConnection();
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement ptmt = null;
		try {
			ptmt = conn.prepareStatement(strsql);
			ptmt.executeUpdate();
			commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			rollback();
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
	}
	
	/**
	 * 更新数据库，有where的情况
	 * @throws Exception 
	 */
	public static void update(String strsql, Object[] objLst) throws Exception{
		conn = getConnection();
		boolean hassqlexception = false;
		String sqlexceptionmsg = "";
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement ptmt = null;
		try {
			ptmt = conn.prepareStatement(strsql);
			preparedstatementsetObject(ptmt, objLst);
			ptmt.executeUpdate();
			commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			rollback();
			hassqlexception = true;
			sqlexceptionmsg = e.getMessage();
		} finally {
			closeStatement(ptmt);
			closeConnection();
		}
		if(hassqlexception){
			handlefailure("数据库异常，异常信息为: " + sqlexceptionmsg);
		}
	}
	
	private static void preparedstatementsetObject(PreparedStatement ptmt, Object[] objLst){
		for(int i=0;i<objLst.length;i++){
			try {
				if(objLst[i] instanceof String){
					ptmt.setString(i+1, objLst[i].toString());
				}else if(objLst[i] instanceof Integer){
					ptmt.setInt(i+1, ((Integer)objLst[i]).intValue());
				}else if(objLst[i] instanceof Double){
					ptmt.setDouble(i+1, ((Double)objLst[i]).doubleValue());
				}else if(objLst[i] instanceof Float){
					ptmt.setFloat(i+1, ((Float)objLst[i]).floatValue());
				}else if(objLst[i] instanceof Long){
					ptmt.setLong(i+1, ((Long)objLst[i]).longValue());
				}else if(objLst[i] instanceof java.util.Date){
					java.util.Date nowDate = (java.util.Date)objLst[i];
					ptmt.setDate(i+1, new java.sql.Date(nowDate.getTime()));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 连接数据库
	 */
	private static Connection getConnection(){
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
			return conn;
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e2){
			e2.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * commit操作
	 */
	private static void commit(){
		try {
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 回滚
	 */
	private static void rollback(){
		try {
			conn.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 关闭ResultSet对象
	 */
	private static void closeResultSet(ResultSet rst){
		try {
			if(rst!=null){
				rst.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 关闭Statement
	 */
	private static void closeStatement(Statement st){
		try {
			if(st!=null){
				st.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 关闭连接
	 */
	private static void closeConnection(){
		try {
			if(conn!=null){
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
    /**
     * 报出错误，对应案例置为fail
     */
    private static void handlefailure(String msg) throws Exception{
    	throw new Exception(msg);
    }
}
